
-- **************************************************************************
--    Project Name:   服务质量-超标件明细
--    Job Name:       jms_dm__dm_fwzl_over_nomarl_waybill_detail
--    Description :   运单获取的dws设备扫描的长、宽、高小于等于备案中的长、宽、高字段（长宽高取最大值进行比较），或者dws设备扫描的重量小于备案中的重量
--    Author :        jimmy 季修魁
--    date：          20223/05/22
-- **************************************************************************
--
--
-- **************************************************************************

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.dynamic.partitions.pernode=1000;

with net_message_tb as (
    select id
           ,code                       as network_code
           ,max(name)                  as network_name
           ,max(agent_code)            as agent_code
           ,max(agent_name)            as agent_name
           ,max(fran_code)             as fran_code
           ,max(fran_name)             as fran_name
           ,max(virt_code)             as virt_code
           ,max(virt_name)             as virt_name
           ,max(network_type)          as network_type
     from jms_dim.dim_network_whole_massage
    group by id, code
    ),

---2.备案信息
express_wo_record_tb as (
    select customer_code
            ,archive_date
            ,max(customer_name) as customer_name
            ,max(length)        as length
            ,max(wide)          as wide
            ,max(high)          as high
            ,max(weight)        as weight
       from jms_dim.dim_problem_express_wo_record_base
      where record_type_id ='300193871256625172'   ---备案类型为‘超标件/超重件收寄申请
        and record_status='3'                      ---流程状态为已归档
        and record_effect_status=1
      group by customer_code,archive_date
    ),

---3.中心到件扫描明细

barscan_arrival_tb as (
    select waybill_no
           ,weight
           ,length
           ,width
           ,height
           ,len_wid_hei_product
           ,source
           ,pistol_id
           ,scan_time
           ,scan_site_code
           ,send_site
           ,network_type
           ,scan_type
           ,case when cast(weight as decimal(18,2))>21 and cast(max_over_cm as decimal(18,2))>80 then 1
                 when cast(weight as decimal(18,2))>21 and cast(max_over_cm as decimal(18,2))<=80 then 2
                 when cast(weight as decimal(18,2))<=21 and cast(max_over_cm as decimal(18,2))>80 then 1
                 else 1
            end as over_nomarl_type
           ,max_over_cm
           ,dt
        from (select waybill_no
                ,weight
                ,length
                ,width
                ,height
                ,(cast(length as decimal(18,2)) * cast(width as decimal(18,2)) * cast(height as decimal(18,2)))/8000   as len_wid_hei_product
                ,source
                ,pistol_id
                ,scan_time
                ,scan_site_code
                ,send_site
                ,network_type
                ,scan_type
                ,row_number() over (partition by waybill_no order by scan_time)      as rn1
                ,sort_array(array(cast(nvl(length,0) as decimal(18,2)),cast(nvl(width,0) as decimal(18,2)),cast(nvl(height,0) as decimal(18,2))))[2]  as max_over_cm
                ,to_date(scan_time) as dt
           from jms_dwd.dwd_tab_barscan_arrival_base_dt
          where dt>=date_add('{{ execution_date | cst_ds }}',-4)
            and dt<='{{ execution_date | cst_ds }}'
            and to_date(scan_time)>= date_add('{{ execution_date | cst_ds }}',-2)
            and to_date(scan_time)<='{{ execution_date | cst_ds }}'
            and waybill_no like 'JT%'
            and source='PC端'
            and scan_type ='中心到件'
            and  pistol_id is not null
            and (cast(height as decimal(18,2)) >80  or cast(width as decimal(18,2)) >80 or cast(length as decimal(18,2)) >80 or cast(weight as decimal(18,2)) >21 )
        )  t1
    ),

---4.运单备案信息

order_waybill_tb as (
    select t1.waybill_no
           ,t1.package_inside_charge_weight      --内部计费重量,单位千克
           ,t1.pick_network_id                   --寄件网点id
           ,t1.pick_network_code                 --寄件网点code
           ,t1.pick_network_name                 --寄件网点名称
           ,t1.delivery_time                     --寄件时间
           ,t1.customer_code                     --客户code
           ,t1.customer_name                     --客户名称
           ,t1.settlement_code                   --结算方式code
           ,t1.settlement_name                   --计算方式名称
           ,t1.dispatch_network_id               --派件网点id
           ,t1.dispatch_network_code             --派件网点code
           ,t1.dispatch_network_name             --派件网点名称
           ,max(t2.length)        as length      --备案最大长度
           ,max(t2.wide)          as wide        --备案最大宽度
           ,max(t2.high)          as high        --备案最大高度
           ,max(t2.weight)        as weight      --备案最大重量
           ,sort_array(array(nvl(max(t2.length),0),nvl(max(t2.wide),0),nvl(max(t2.high),0)))  as array_sort_2
      from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt t1
      left join (select customer_code
                       ,archive_date
                       ,max(customer_name) as customer_name
                       ,max(length)        as length
                       ,max(wide)          as wide
                       ,max(high)          as high
                       ,max(weight)        as weight
                  from jms_dim.dim_problem_express_wo_record_base
                 where record_type_id ='300193871256625172'   ---备案类型为‘超标件/超重件收寄申请
                   and record_status='3'                      ---流程状态为已归档
                   and record_effect_status=1
                 group by customer_code,archive_date  ) t2
        on t1.customer_code = t2.customer_code
       and t1.delivery_time > t2.archive_date
     where t1.dt >= date_add('{{ execution_date | cst_ds }}', -16)
       and t1.dt <= '{{ execution_date | cst_ds }}'
    group by t1.waybill_no
             ,t1.package_inside_charge_weight
             ,t1.pick_network_id
             ,t1.pick_network_code
             ,t1.pick_network_name
             ,t1.delivery_time
             ,t1.customer_code
             ,t1.customer_name
             ,t1.settlement_code
             ,t1.settlement_name
             ,t1.dispatch_network_id
             ,t1.dispatch_network_code
             ,t1.dispatch_network_name
    )

insert overwrite table jms_dm.dm_fwzl_over_nomarl_waybill_detail partition (dt)
select t1.waybill_no                      as    bill_code
       ,t1.scan_time                      as    arrival_scan_time
       ,t1.scan_site_code                 as    scan_site_code
       ,t3.network_name                   as    scan_site_name
       ,t3.agent_code                     as    scan_site_agent_code
       ,t3.agent_name                     as    scan_site_agent_name
       ,t1.pistol_id                      as    pistol_id
       ,t1.weight                         as    weight
       ,t1.length                         as    length
       ,t1.width                          as    width
       ,t1.height                         as    height
       ,t1.len_wid_hei_product            as    len_wid_hei_product
       ,t2.package_inside_charge_weight   as    package_inside_charge_weight
       ,t2.pick_network_id                as    pick_network_id
       ,t2.pick_network_code              as    pick_network_code
       ,t2.pick_network_name              as    pick_network_name
       ,t4.agent_code                     as    pick_agent_code
       ,t4.agent_name                     as    pick_agent_name
       ,t4.virt_code                      as    pick_virt_code
       ,t4.virt_name                      as    pick_virt_name
       ,t2.delivery_time                  as    delivery_time
       ,t2.customer_code                  as    customer_code
       ,t2.customer_name                  as    customer_name
       ,t2.settlement_code                as    settlement_code
       ,t2.settlement_name                as    settlement_name
       ,t2.dispatch_network_id            as    dispatch_network_id
       ,t2.dispatch_network_code          as    dispatch_network_code
       ,t2.dispatch_network_name          as    dispatch_network_name
       ,t5.agent_code                     as    dispatch_agent_code
       ,t5.agent_name                     as    dispatch_agent_name
       ,t5.virt_code                      as    dispatch_virt_code
       ,t5.virt_name                      as    dispatch_virt_name
       ,t1.over_nomarl_type               as    over_nomarl_type
       ,case when t2.array_sort_2[2]>=t1.max_over_cm or t2.weight >=t1.weight then 1 else 0 end is_record
       ,t1.dt                             as dt
  from barscan_arrival_tb      t1
  left join order_waybill_tb   t2
    on t1.waybill_no = t2.waybill_no
  left join net_message_tb     t3
    on t1.scan_site_code = t3.network_code
  left join net_message_tb     t4
    on t2.pick_network_code = t4.network_code
  left join net_message_tb     t5
    on t2.dispatch_network_code = t5.network_code
  distribute by pmod(hash(bill_code), 4)
   ;